<?php

class m_makul extends CI_Model {

    function __construct() {
        // Call the Model constructor
        parent::__construct();
    }

    // get total data makul
    function get_total_data_makul($params) {
        $sql = "SELECT COUNT(*) AS 'total' 
            FROM mata_kuliah 
            WHERE makul_nm LIKE ?";
        $query = $this->db->query($sql, $params);
        if ($query->num_rows() > 0) {
            $result = $query->row_array();
            $query->free_result();
            return $result['total'];
        } else {
            return array();
        }
    }

    // get data makul
    function get_data_makul($params) {
        $sql = "SELECT * FROM mata_kuliah 
            WHERE makul_nm LIKE ? 
            ORDER BY makul_nm ASC 
            LIMIT ?, ?";
        $query = $this->db->query($sql, $params);
        if ($query->num_rows() > 0) {
            $result = $query->result_array();
            $query->free_result();
            return $result;
        } else {
            return array();
        }
    }

    // get all data makul
    function get_all_data_makul() {
        $sql = "SELECT * FROM mata_kuliah 
            ORDER BY makul_nm ASC";
        $query = $this->db->query($sql);
        if ($query->num_rows() > 0) {
            $result = $query->result_array();
            $query->free_result();
            return $result;
        } else {
            return array();
        }
    }

    // get detail data makul
    function get_detail_data_makul($params) {
        $sql = "SELECT * FROM mata_kuliah 
            WHERE makul_kd = ?";
        $query = $this->db->query($sql, $params);
        if ($query->num_rows() > 0) {
            $result = $query->row_array();
            $query->free_result();
            return $result;
        } else {
            return array();
        }
    }

    // insert data makul
    function insert_data_makul($params) {
        $sql = "INSERT INTO mata_kuliah (makul_kode, makul_nm, makul_sks, mdb, mdd) 
            VALUES(?, ?, ?, ?, NOW())";
        return $this->db->query($sql, $params);
    }

    // update data makul
    function update_data_makul($params) {
        $sql = "UPDATE mata_kuliah SET makul_kode = ?, makul_nm = ?, makul_sks = ?, mdb = ?, mdd = NOW() 
            WHERE makul_kd = ?";
        return $this->db->query($sql, $params);
    }

    // delete data makul
    function delete_data_makul($params) {
        $sql = "DELETE FROM mata_kuliah WHERE makul_kd = ?";
        return $this->db->query($sql, $params);
    }

    /*
     * PENGATURAN MATA KULIAH
     */

    // get data group makul
    function get_data_group_makul() {
        $sql = "SELECT * FROM setting_makul 
            ORDER BY tahun DESC, semester DESC";
        $query = $this->db->query($sql);
        if ($query->num_rows() > 0) {
            $result = $query->result_array();
            $query->free_result();
            return $result;
        } else {
            return array();
        }
    }

    // get detail setting
    function get_detail_setting_makul($params) {
        $sql = "SELECT * FROM setting_makul 
            WHERE set_makul_kd = ?";
        $query = $this->db->query($sql, $params);
        if ($query->num_rows() > 0) {
            $result = $query->row_array();
            $query->free_result();
            return $result;
        } else {
            return array();
        }
    }

    // get total data makul per group
    function get_total_data_set_makul_detail($params) {
        $sql = "SELECT COUNT(*) AS 'total' 
            FROM setting_makul_detail a 
            JOIN mata_kuliah b ON a.makul_kd = b.makul_kd 
            WHERE set_makul_kd = ? AND b.makul_nm LIKE ?";
        $query = $this->db->query($sql, $params);
        if ($query->num_rows() > 0) {
            $result = $query->row_array();
            $query->free_result();
            return $result['total'];
        } else {
            return 0;
        }
    }

    // get data makul per group
    function get_data_set_makul_detail($params) {
        $sql = "SELECT a.*, b.makul_nm  
            FROM setting_makul_detail a 
            JOIN mata_kuliah b ON a.makul_kd = b.makul_kd 
            WHERE set_makul_kd = ? AND b.makul_nm LIKE ? 
            ORDER BY b.makul_nm ASC 
            LIMIT ?, ?";
        $query = $this->db->query($sql, $params);
        if ($query->num_rows() > 0) {
            $result = $query->result_array();
            $query->free_result();
            return $result;
        } else {
            return array();
        }
    }

    // get detail data set makul
    function get_detail_data_set_makul_detail($params) {
        $sql = "SELECT a.*, b.makul_nm  
            FROM setting_makul_detail a 
            JOIN mata_kuliah b ON a.makul_kd = b.makul_kd 
            WHERE set_makul_kd = ? AND b.makul_kd = ? 
            ORDER BY b.makul_nm ASC";
        $query = $this->db->query($sql, $params);
        if ($query->num_rows() > 0) {
            $result = $query->row_array();
            $query->free_result();
            return $result;
        } else {
            return array();
        }
    }

    // cek setting makul
    function is_setting_makul_exist($params) {
        $sql = "SELECT COUNT(*) AS 'total' 
            FROM setting_makul 
            WHERE tahun = ? AND semester = ?";
        $query = $this->db->query($sql, $params);
        if ($query->num_rows() > 0) {
            $result = $query->row_array();
            $query->free_result();
            return $result['total'];
        } else {
            return 1;
        }
    }

    // cek detail set makul
    function is_detail_setting_makul_exist($params) {
        $sql = "SELECT COUNT(*) AS 'total' 
            FROM setting_makul_detail 
            WHERE set_makul_kd = ? AND makul_kd = ?";
        $query = $this->db->query($sql, $params);
        if ($query->num_rows() > 0) {
            $result = $query->row_array();
            $query->free_result();
            return $result['total'];
        } else {
            return 1;
        }
    }

    // insert setting makul
    function insert_setting_makul($params) {
        $sql = "INSERT INTO setting_makul (semester, tahun, mdb, mdd) 
            VALUES(?, ?, ?, NOW())";
        return $this->db->query($sql, $params);
    }

    // insert detail setting makul
    function insert_detail_setting_makul($params) {
        $sql = "INSERT INTO setting_makul_detail (set_makul_kd, makul_kd, set_jur_1, set_jur_2, set_jur_3, set_jur_4, mdb, mdd) 
            VALUES(?, ?, ?, ?, ?, ?, ?, NOW())";
        return $this->db->query($sql, $params);
    }

    // delete detail setting makul
    function delete_detail_setting_makul($params) {
        $sql = "DELETE FROM setting_makul_detail 
            WHERE set_makul_kd = ? AND makul_kd = ?";
        return $this->db->query($sql, $params);
    }

    /*
     *  MONITORING
     */

    // get total dosen
    function get_total_dosen($params) {
        $sql = "SELECT COUNT(*) AS 'total' FROM
                (
                    SELECT a.* FROM users a 
                    JOIN com_role_user b ON a.user_id = b.user_id 
                    JOIN com_role c ON b.role_id = c.role_id 
                    LEFT JOIN kelas d ON a.user_id = d.dosen_kd
                    WHERE c.role_id = 4 AND d.tahun = ?  
                    AND d.semester = ? AND a.nama_lengkap LIKE ?  
                    GROUP BY a.user_id
                )r";
        $query = $this->db->query($sql, $params);
        if ($query->num_rows() > 0) {
            $result = $query->row_array();
            $query->free_result();
            return $result['total'];
        } else {
            return array();
        }
    }

    // get data dosen
    function get_data_dosen($params) {
        $sql = "SELECT a.*, d.tahun, d.semester FROM users a 
                JOIN com_role_user b ON a.user_id = b.user_id 
                JOIN com_role c ON b.role_id = c.role_id 
                LEFT JOIN kelas d ON a.user_id = d.dosen_kd
                WHERE c.role_id = 4 AND d.tahun = ?  
                AND d.semester = ? AND a.nama_lengkap LIKE ? 
                GROUP BY a.user_id 
                ORDER BY a.nama_lengkap ASC 
                LIMIT ?, ?";
        $query = $this->db->query($sql, $params);
        if ($query->num_rows() > 0) {
            $result = $query->result_array();
            $query->free_result();
            return $result;
        } else {
            return array();
        }
    }

    // get total makul per dosen
    function get_total_makul_dosen($params) {
        $sql = "SELECT COUNT(*) AS 'total' FROM setting_dosen_ajar a 
                JOIN setting_dosen_ajar_detail b ON a.set_dosenajar_kd = b.set_dosenajar_kd 
                WHERE a.tahun = ? AND a.semester = ?  
                AND b.dosen_kd = ?";
        $query = $this->db->query($sql, $params);
        if ($query->num_rows() > 0) {
            $result = $query->row_array();
            $query->free_result();
            return $result['total'];
        } else {
            return 0;
        }
    }

    // get total kelas per dosen
    function get_total_kelas_dosen($params) {
        $sql = "SELECT COUNT(*) AS 'total' FROM kelas 
                WHERE tahun = ? AND semester = ?  
                AND dosen_kd = ?";
        $query = $this->db->query($sql, $params);
        if ($query->num_rows() > 0) {
            $result = $query->row_array();
            $query->free_result();
            return $result['total'];
        } else {
            return 0;
        }
    }

    // get total kelas per makul
    function get_total_kelas_makul($params) {
        $sql = "SELECT COUNT(*) AS 'total' FROM kelas 
                WHERE tahun = ? AND semester = ?  
                AND dosen_kd = ? AND makul_kd = ?";
        $query = $this->db->query($sql, $params);
        if ($query->num_rows() > 0) {
            $result = $query->row_array();
            $query->free_result();
            return $result['total'];
        } else {
            return 0;
        }
    }

    // get total peremuan per makul
    function get_total_pertemuan_makul($params) {
        $sql = "SELECT COUNT(*) AS 'total' FROM kelas a 
                JOIN kelas_detail b ON a.kelas_kd = b.kelas_kd 
                WHERE tahun = ? AND semester = ?  
                AND dosen_kd = ? AND makul_kd = ?";
        $query = $this->db->query($sql, $params);
        if ($query->num_rows() > 0) {
            $result = $query->row_array();
            $query->free_result();
            return $result['total'];
        } else {
            return 0;
        }
    }

    // get makul diampu
    function get_makul_diampu($params) {
        $sql = "SELECT b.*, d.makul_nm FROM setting_dosen_ajar a 
                JOIN setting_dosen_ajar_detail b ON a.set_dosenajar_kd = b.set_dosenajar_kd 
                JOIN users c ON b.dosen_kd = c.user_id 
                JOIN mata_kuliah d ON b.makul_kd = d.makul_kd 
                WHERE a.tahun = ? AND a.semester = ?  
                AND b.dosen_kd = ?";
        $query = $this->db->query($sql, $params);
        if ($query->num_rows() > 0) {
            $result = $query->result_array();
            $query->free_result();
            return $result;
        } else {
            return array();
        }
    }

    // get data makul dosen
    function get_data_makul_dosen($params) {
        $sql = "SELECT c.* FROM setting_dosen_ajar a 
                JOIN setting_dosen_ajar_detail b ON a.set_dosenajar_kd = b.set_dosenajar_kd 
                JOIN mata_kuliah c ON b.makul_kd = c.makul_kd 
                WHERE a.tahun = ? AND a.semester = ?   
                AND b.dosen_kd = ?";
        $query = $this->db->query($sql, $params);
        if ($query->num_rows() > 0) {
            $result = $query->result_array();
            $query->free_result();
            return $result;
        } else {
            return array();
        }
    }

    // get list detail perkuliahan
    function get_list_detail_perkuliahan($params) {
        $sql = "SELECT a.kelas_nm, b.*, c.kompetensi_dasar 
                FROM kelas a 
                JOIN kelas_detail b ON a.kelas_kd = b.kelas_kd 
                JOIN silabus_detail c ON b.silabus_detail_kd = c.silabus_detail_kd 
                WHERE a.tahun = ? AND a.semester = ?  
                AND a.dosen_kd = ? AND a.makul_kd = ? 
                ORDER BY a.kelas_nm ASC, b.kelas_minggu ASC";
        $query = $this->db->query($sql, $params);
        if ($query->num_rows() > 0) {
            $result = $query->result_array();
            $query->free_result();
            return $result;
        } else {
            return array();
        }
    }

}